package com.itheima.mapper.backend;

import com.itheima.vo.HotSetmeal2VO;
import com.itheima.vo.SetmealCountVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * 统计报表服务持久层
 */
@Mapper
public interface ReportMapper {

    //查询会员的最早注册日期
    @Select("select min(regtime) from t_member")
    Date minRegTime();

    //查询会员的会员总数
    @Select("select count(id) from t_member where regTime < #{regTime}")
    Integer findmemberCounts(Date regTime);

    //查询预约套餐信息
    @Select("select t_setmeal.name,count(t_order.id) as value " +
            "from t_order,t_setmeal where t_order.setmeal_id=t_setmeal.id group by t_setmeal.name order by t_setmeal.name")
    List<SetmealCountVO> findSetmealNumber();

    //查询运营数据
    @Select("select count(id) from t_member where regtime = #{today}")
    Integer todayNewMember(Date today);//今日新增会员数

    @Select("select count(id) from t_member")
    Integer totalMember();//总会员数

    @Select("select count(id) from t_member where regtime between #{weekMonday} and #{thisWeekMonday}")
    Integer thisWeekNewMember(Date weekMonday, Date thisWeekMonday);//本周新增会员数

    @Select("select count(id) from t_member where regtime between #{thisMonthFirstDay} and #{thisMonthLastDay}")
    Integer thisMonthNewMember(Date thisMonthFirstDay);//本月新增会员数

    @Select("select count(id) from t_order where orderDate = #{today}")
    Integer todayOrderNumber(Date today);//今日预约数

    @Select("select count(id) from t_order where orderDate = #{today} and orderStatus='已到诊'")
    Integer todayVisitsNumber(Date today);//今日到诊数

    @Select("select count(id) from t_order where orderDate between #{thisWeekMonday} and #{thisWeekSunday}")
    Integer thisWeekOrderNumber(Date thisWeekMonday, Date thisWeekSunday);//本周预约数

    @Select("select count(id) from t_order where orderDate between (#{thisWeekMonday} and #{thisWeekSunday}) and orderStatus='已到诊'")
    Integer thisWeekVisitsNumber(Date thisWeekMonday, Date thisWeekSunday);//本周到诊数

    @Select("select count(id) from t_order where orderDate between #{thisMonthFirstDay} and #{thisMonthLastDay}")
    Integer thisMonthOrderNumber(Date thisMonthFirstDay, Date thisMonthLastDay);//本月预约数

    @Select("select count(id) from t_order where orderDate between (#{thisMonthFirstDay} and #{thisMonthLastDay}) and orderStatus='已到诊'")
    Integer thisMonthVisitsNumber(Date thisMonthFirstDay, Date thisMonthLastDay);//本月到诊数

    @Select("select t_setmeal.name,count(t_order.id) as setmeal_count from t_order,t_setmeal " +
            "where t_setmeal.id = t_order.setmeal_id group by t_setmeal.name order by setmeal_count desc limit 0,4")
    List<HotSetmeal2VO> hotSetmeal();//获取热门套餐

    @Select("select count(id) from t_order")//获取套餐总数
    Integer orderSum();
}
